package com.es.pay.ctmPay;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class CtmPayBiz {

	public void initCtmExam() throws Exception{
		Dao dao = new Dao();
		try {
			String sql = "insert into hrm_ctm_exam(emp_id,yyyy_mm,ctm_code,ctm_name,area_value,area_code,rtm_name,rtm_code) "
						+" select emp_id ,convert(varchar(7),getdate(),23) yyyy_mm,emp.emp_code,emp.emp_name,emp.emp_area_value,emp_area_code,emp_rtm_name,"
						+" (select top 1 a.emp_code from hrm_emp_info  a where a.emp_id = emp.emp_rtm_code) emp_rtm_code " 
						+" from hrm_emp_info emp where emp.emp_role='CTM' and emp.emp_shiyebu='ITMALL' and emp.emp_rtm_code is not  null "
						+" and  not exists (select * from hrm_ctm_exam where yyyy_mm = convert(varchar(7),getdate(),23))  ";
			dao.update(sql, null);
		} finally{
			dao.close();
		}
	}
	
	public ArrayList<CtmPayBean> countCtmNum() throws Exception{
		Dao dao = new Dao();
		ArrayList<CtmPayBean> result = new ArrayList<CtmPayBean>();
		try {
			String sql = "select a.area_value,cast(a.total as varchar) total,cast(b.target as varchar) target ,cast(c.ctm as varchar) ctm,cast(d.visitor as varchar) visitor,cast(e.shop as varchar) shop from "
							+" (select  area_value,count(distinct ctm_name) total from hrm_ctm_exam where yyyy_mm = convert(varchar(7),getdate(),23)"
							+" group by area_value) a,"
							+" (select area_value,count( distinct ctm_name) target from hrm_target_rate where yyyy_mm = convert(varchar(7),getdate(),23)" 
							+" group by area_value)b,"
							+" (select  emp_area_value area_value,count( distinct emp_name) ctm from hrm_ctm_grade where yyyy_mm = convert(varchar(7),getdate(),23) and grade_totall is not null"
							+" group by emp_area_value)c,"
							+" (select  visitor_area area_value,count(distinct v_oversee) visitor from hrm_mystery_visitor_grade where v_date = convert(varchar(7),getdate(),23)"
							+" group by visitor_area)d,"
							+" (select   S_AREA area_value,count(distinct S_OVERSEE)shop  from STOREFRONT_GRADE where s_data = convert(varchar(7),getdate(),23) group by S_AREA)e"
							+" where a.area_value *= b.area_value"
							+" and a.area_value *= c.area_value"
							+" and a.area_value *= d.area_value"
							+" and a.area_value *= e.area_value";	
			ResultSet resultSet = dao.Select(sql);
			int total = 0;
			int target = 0;
			int ctm = 0;
			int visitor = 0;
			int shop = 0;
			while(resultSet.next()){
				CtmPayBean bean = new CtmPayBean();
				bean.setArea_value(resultSet.getString("area_value"));
				bean.setTotal(resultSet.getString("total"));				
				bean.setTarget(resultSet.getString("target"));				
				bean.setCtm(resultSet.getString("ctm"));				
				bean.setVisitor(resultSet.getString("visitor"));				
				bean.setShop(resultSet.getString("shop"));
				result.add(bean);
				
				//合计
				ctm+=Integer.parseInt(resultSet.getString("ctm")==null?"0":resultSet.getString("ctm"));
				target+=Integer.parseInt(resultSet.getString("target")==null?"0":resultSet.getString("target"));
				total+=Integer.parseInt(resultSet.getString("total")==null?"0":resultSet.getString("total"));
				visitor+=Integer.parseInt(resultSet.getString("visitor")==null?"0":resultSet.getString("visitor"));
				shop+=Integer.parseInt(resultSet.getString("shop")==null?"0":resultSet.getString("shop"));
			}
			CtmPayBean bean = new CtmPayBean();
			bean.setArea_value("合计");
			bean.setTotal(String.valueOf(total));				
			bean.setTarget(String.valueOf(target));				
			bean.setCtm(String.valueOf(ctm));				
			bean.setVisitor(String.valueOf(visitor));				
			bean.setShop(String.valueOf(shop));
			result.add(bean);
		} finally{
			dao.close();
		}
		return result;
	}
	
	
	public boolean createCtmGrade() throws Exception {
		Dao dao = new Dao();
		boolean bool = false;
		try {
			String sql = ("{call count_ctm_grade}");
			dao.executePro(sql);
			bool = true;
		} catch (Exception e) {
			System.out.print(e.getMessage());
		} finally {
			dao.close();
		}
		return bool;
	}
	
	public PageBean queryCtmGradeList(PageNavigator pageNav, ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		PageBean pageBean = new  PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select *  from hrm_ctm_exam where '1'='1'";
			String[] sqlArrary	= new String[] {"and  yyyy_mm=? ",
						"and area_code = ? ",
						"and UPPER(ctm_name) like '%'+UPPER(?)+'%' ",
						"and UPPER(ctm_code) like '%'+UPPER(?)+'%' ",
						"and UPPER(rtm_code) like '%'+UPPER(?)+'%' ",
						"and UPPER(rtm_name) like '%'+UPPER(?)+'%' "};
			for (int i = 0; i < param.size(); ++i) {
			      if(!param.get(i).equals("")){
			    	  sql += sqlArrary[i];
			    	  query.add(param.get(i).trim());
			      }
			  }
			int total = dao.getAllRow(sql,query);		
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total%pageNav.getPageRow()==0?total/pageNav.getPageRow():total/pageNav.getPageRow()+1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally{
			dao.close();
		}
		return pageBean;
	}
	
	public ArrayList<HashMap<String,String>> queryCtmGradeList( ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select *  from hrm_ctm_exam where '1'='1'";
			String[] sqlArrary	= new String[] {"and  yyyy_mm=? ",
						"and area_code = ? ",
						"and UPPER(ctm_name) like '%'+UPPER(?)+'%' ",
						"and UPPER(ctm_code) like '%'+UPPER(?)+'%' ",
						"and UPPER(rtm_code) like '%'+UPPER(?)+'%' ",
						"and UPPER(rtm_name) like '%'+UPPER(?)+'%' "};
			for (int i = 0; i < param.size(); ++i) {
			      if(!param.get(i).equals("")){
			    	  sql += sqlArrary[i];
			    	  query.add(param.get(i).trim());
			      }
			  }
			ResultSet resultSet = null;
			if(query.size()==0)
				resultSet = dao.Select(sql);
			else
				resultSet = dao.Select(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData();
			
			while(resultSet.next()){
					HashMap<String,String> map = new HashMap<String,String>();
					for(int i = 0;i<rss.getColumnCount(); i++){ 	
						map.put(rss.getColumnName(i+1), (String) resultSet.getObject(rss.getColumnName(i+1)));							
				 }	
				  list.add(map);
			  }
		} finally{
			dao.close();
		}
		return list;
	}
	
	public ArrayList<HashMap<String,String>> getYYYYList() throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		try {
			String sql = "select distinct yyyy_mm code,yyyy_mm value  from hrm_ctm_exam ";
			ResultSet resultSet = dao.Select(sql);
			while(resultSet.next()){
					HashMap<String,String> map = new HashMap<String,String>();
					map.put("code", resultSet.getString("code"));
					map.put("value", resultSet.getString("value"));
				  list.add(map);
			  }
		} finally{
			dao.close();
		}
		return list;
	}
}

